package com.returnsoft.resource.eao;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.ejb.LocalBean;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.TypedQuery;

import com.returnsoft.resource.dto.AreaDto;
import com.returnsoft.resource.dto.CandidateDto;
import com.returnsoft.resource.dto.InterviewDto;
import com.returnsoft.resource.dto.InterviewStateDto;
import com.returnsoft.resource.dto.RecruiterDto;
import com.returnsoft.resource.entity.Interview;
import com.returnsoft.resource.exception.EaoExcepcion;

@LocalBean
@Stateless
public class InterviewEao {

	@PersistenceContext
	private EntityManager em;

	@SuppressWarnings("unchecked")
	public List<InterviewDto> findList(List<Integer> areasId,
			List<Integer> subAreasId,String interviewedAtFormatter,
			Integer stateId, Integer recruiterId,
			String candidateDocumentNumber, String candidateFirstname,
			String candidateLastname) throws EaoExcepcion {
		try {

			String query = "select i.id,i.interviewed_at,i.created_at,i.comment, "
					+ "ist.id,ist.name, "
					+ "c.id,c.document_type,c.document_number,c.firstname,c.lastname, "
					+ "r.id,p.firstname,p.lastname,"
					+ "a.id,a.name,ap.id,ap.name "
					+ "from recruitment.interview i "
					+ "left join recruitment.interview_state ist on ist.id=i.interview_state_id "
					+ "left join recruitment.candidate c on c.id=i.candidate_id "
					+ "left join recruitment.area a on a.id=c.area_id "
					+ "left join recruitment.area ap on ap.id=a.area_id "
					+ "left join recruitment.recruiter r on r.id=i.recruiter_id "
					+ "left join security.user u on u.id=r.user_id "
					+ "left join security.person p on p.id=u.person_id "
					+ "where i.id>0 ";

			if (interviewedAtFormatter != null
					&& !interviewedAtFormatter.trim().equals("")) {
				query += " and i.interviewed_at like '%"
						+ interviewedAtFormatter + "%' ";
			}

			if (stateId != null && stateId > 0) {
				query += " and ist.id =  " + stateId + " ";
			}

			if (recruiterId != null && recruiterId > 0) {
				query += " and i.recruiter_id = " + recruiterId + " ";
			}
			
			if (areasId != null && areasId.size() > 0) {
				query += " and ap.id in ";
				query += " ( ";
				for (int i = 0; i < areasId.size(); i++) {
					Integer areaId = areasId.get(i);
					if (i < areasId.size() - 1) {
						query += areaId + ",";
					} else {
						query += areaId;
					}
				}
				query += " ) ";
			}else {  query += " and ap.id is null "; }
			
			if (subAreasId != null && subAreasId.size() > 0) {
				query += " and a.id in ";
				query += " ( ";
				for (int i = 0; i < subAreasId.size(); i++) {
					Integer subAreaId = subAreasId.get(i);
					if (i < subAreasId.size() - 1) {
						query += subAreaId + ",";
					} else {
						query += subAreaId;
					}
				}
				query += " ) ";
			}

			if (candidateDocumentNumber != null
					&& !candidateDocumentNumber.trim().equals("")) {
				query += " and c.document_number like '%"
						+ candidateDocumentNumber + "%' ";
			}

			if (candidateFirstname != null
					&& !candidateFirstname.trim().equals("")) {
				query += " and c.firstname like '%" + candidateFirstname
						+ "%' ";
			}

			if (candidateLastname != null
					&& !candidateLastname.trim().equals("")) {
				query += " and c.lastname like '%" + candidateLastname + "%' ";
			}
			
			query += " order by i.id desc"; 

			Query q = em.createNativeQuery(query);

			List<Object[]> objectsList = (List<Object[]>) q.getResultList();

			List<InterviewDto> entities = new ArrayList<InterviewDto>();
			if (objectsList.size() > 0) {
				for (Object[] object : objectsList) {

					Integer id = (Integer) object[0];
					Date interviewedAt = (Date) object[1];
					Date createdAt = (Date) object[2];
					String comment = (String) object[3];
					stateId = (Integer) object[4];
					String stateName = (String) object[5];

					Integer candidateId = (Integer) object[6];
					String candidateTypeDocumentNumber = (String) object[7];
					candidateDocumentNumber = (String) object[8];
					candidateFirstname = (String) object[9];
					candidateLastname = (String) object[10];

					recruiterId = (Integer) object[11];
					String recruiterFirstname = (String) object[12];
					String recruiterLastname = (String) object[13];
					
					Integer candidateAreaId = (Integer) object[14];
					String candidateAreaName = (String) object[15];
					Integer candidateAreaParentId = (Integer) object[16];
					String candidateAreaParentName = (String) object[17];

					InterviewDto entity = new InterviewDto();
					entity.setId(id);
					entity.setComment(comment);
					entity.setInterviewedAt(interviewedAt);
					entity.setCreatedAt(createdAt);
					if (stateId != null && stateId > 0) {
						InterviewStateDto state = new InterviewStateDto();
						state.setId(stateId);
						state.setName(stateName);
						entity.setInterviewState(state);
					}
					if (recruiterId != null && recruiterId > 0) {
						RecruiterDto recruiter = new RecruiterDto();
						recruiter.setId(recruiterId);
						recruiter.setFirstname(recruiterFirstname);
						recruiter.setLastname(recruiterLastname);
						entity.setRecruiter(recruiter);
					}
					if (candidateId != null && candidateId > 0) {
						CandidateDto candidate = new CandidateDto();
						candidate.setId(candidateId);
						candidate.setDocumentType(candidateTypeDocumentNumber);
						candidate.setDocumentNumber(candidateDocumentNumber);
						candidate.setFirstname(candidateFirstname);
						candidate.setLastname(candidateLastname);
						if (candidateAreaId != null) {
							AreaDto area = new AreaDto();
							area.setId(candidateAreaId);
							area.setName(candidateAreaName);
							if (candidateAreaParentId != null) {
								AreaDto areaParent = new AreaDto();
								areaParent.setId(candidateAreaParentId);
								areaParent.setName(candidateAreaParentName);
								area.setArea(areaParent);
							}
							candidate.setArea(area);
						}
						entity.setCandidate(candidate);
					}

					entities.add(entity);

				}
			}
			return entities;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}
	}

	public Interview add(Interview interview) throws EaoExcepcion {
		try {

			interview = em.merge(interview);

			em.flush();

			return interview;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}
	}

	public Interview findById(Integer id) throws EaoExcepcion {
		try {
			TypedQuery<Interview> q = em.createQuery(
					"SELECT i FROM Interview i WHERE i.id = :id",
					Interview.class);
			q.setParameter("id", id);
			Interview entity = q.getSingleResult();

			return entity;
		} catch (NoResultException e) {
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}
	}

	/*@SuppressWarnings("unchecked")
	public List<InterviewDto> findByDocumentNumber(String documentNumber)
			throws EaoExcepcion {
		try {

			String query = "select i.id,i.interviewed_at,i.created_at,i.comment, "
					+ "ist.id,ist.name, "
					+ "c.id,c.document_type,c.document_number,c.firstname,c.lastname, "
					+ "r.id,p.firstname,p.lastname "
					+ "from recruitment.interview i "
					+ "left join recruitment.interview_state ist on ist.id=i.interview_state_id "
					+ "left join recruitment.candidate c on c.id=i.candidate_id "
					+ "left join recruitment.recruiter r on r.id=i.recruiter_id "
					+ "left join security.user u on u.id=r.user_id "
					+ "left join security.person p on p.id=u.person_id where i.id>0 and c.document_number='"
					+ documentNumber + "'";

			Query q = em.createNativeQuery(query);

			List<Object[]> objectsList = (List<Object[]>) q.getResultList();

			List<InterviewDto> entities = new ArrayList<InterviewDto>();
			if (objectsList.size() > 0) {
				for (Object[] object : objectsList) {

					Integer id = (Integer) object[0];
					Date interviewedAt = (Date) object[1];
					Date createdAt = (Date) object[2];
					String comment = (String) object[3];
					Integer stateId = (Integer) object[4];
					String stateName = (String) object[5];

					Integer candidateId = (Integer) object[6];
					String candidateTypeDocumentNumber = (String) object[7];
					String candidateDocumentNumber = (String) object[8];
					String candidateFirstname = (String) object[9];
					String candidateLastname = (String) object[10];

					Integer recruiterId = (Integer) object[11];
					String recruiterFirstname = (String) object[12];
					String recruiterLastname = (String) object[13];

					InterviewDto entity = new InterviewDto();
					entity.setId(id);
					entity.setComment(comment);
					entity.setInterviewedAt(interviewedAt);
					entity.setCreatedAt(createdAt);
					if (stateId != null && stateId > 0) {
						InterviewStateDto state = new InterviewStateDto();
						state.setId(stateId);
						state.setName(stateName);
						entity.setInterviewState(state);
					}
					if (recruiterId != null && recruiterId > 0) {
						RecruiterDto recruiter = new RecruiterDto();
						recruiter.setId(recruiterId);
						recruiter.setFirstname(recruiterFirstname);
						recruiter.setLastname(recruiterLastname);
						entity.setRecruiter(recruiter);
					}
					if (candidateId != null && candidateId > 0) {
						CandidateDto candidate = new CandidateDto();
						candidate.setId(candidateId);
						candidate.setDocumentType(candidateTypeDocumentNumber);
						candidate.setDocumentNumber(candidateDocumentNumber);
						candidate.setFirstname(candidateFirstname);
						candidate.setLastname(candidateLastname);
						entity.setCandidate(candidate);
					}

					entities.add(entity);

				}
			}

			return entities;
		} catch (NoResultException e) {
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}
	}*/
	
	
	
	@SuppressWarnings("unchecked")
	public List<InterviewDto> findPendingsByCandidateId(Integer candidateId)
			throws EaoExcepcion {
		try {

			String query = "select i.id,i.interviewed_at,i.created_at,i.comment, "
					+ "ist.id,ist.name "
					//+ "c.id,c.document_type,c.document_number,c.firstname,c.lastname, "
					//+ "r.id,p.firstname,p.lastname "
					+ "from recruitment.interview i "
					+ "left join recruitment.interview_state ist on ist.id=i.interview_state_id "
					+ "left join recruitment.candidate c on c.id=i.candidate_id "
					//+ "left join recruitment.recruiter r on r.id=i.recruiter_id "
					//+ "left join security.user u on u.id=r.user_id "
					//+ "left join security.person p on p.id=u.person_id "
					+ "where i.id>0 and ist.is_pending=true and c.id="
					+ candidateId + " order by i.created_at ";

			Query q = em.createNativeQuery(query);

			List<Object[]> objectsList = (List<Object[]>) q.getResultList();

			List<InterviewDto> entities = new ArrayList<InterviewDto>();
			if (objectsList.size() > 0) {
				for (Object[] object : objectsList) {

					Integer id = (Integer) object[0];
					Date interviewedAt = (Date) object[1];
					Date createdAt = (Date) object[2];
					String comment = (String) object[3];
					Integer stateId = (Integer) object[4];
					String stateName = (String) object[5];

					/*candidateId = (Integer) object[6];
					String candidateTypeDocumentNumber = (String) object[7];
					String candidateDocumentNumber = (String) object[8];
					String candidateFirstname = (String) object[9];
					String candidateLastname = (String) object[10];*/

					/*Integer recruiterId = (Integer) object[11];
					String recruiterFirstname = (String) object[12];
					String recruiterLastname = (String) object[13];*/

					InterviewDto entity = new InterviewDto();
					entity.setId(id);
					entity.setComment(comment);
					entity.setInterviewedAt(interviewedAt);
					entity.setCreatedAt(createdAt);
					if (stateId != null && stateId > 0) {
						InterviewStateDto state = new InterviewStateDto();
						state.setId(stateId);
						state.setName(stateName);
						entity.setInterviewState(state);
					}
					/*if (recruiterId != null && recruiterId > 0) {
						RecruiterDto recruiter = new RecruiterDto();
						recruiter.setId(recruiterId);
						recruiter.setFirstname(recruiterFirstname);
						recruiter.setLastname(recruiterLastname);
						entity.setRecruiter(recruiter);
					}*/
					/*if (candidateId != null && candidateId > 0) {
						CandidateDto candidate = new CandidateDto();
						candidate.setId(candidateId);
						candidate.setDocumentType(candidateTypeDocumentNumber);
						candidate.setDocumentNumber(candidateDocumentNumber);
						candidate.setFirstname(candidateFirstname);
						candidate.setLastname(candidateLastname);
						entity.setCandidate(candidate);
					}*/

					entities.add(entity);

				}
			}

			return entities;
		} catch (NoResultException e) {
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}
	}

}
